BEGIN;

-- destruction des tables
DELETE FROM classe_matiere_link;
DELETE FROM eleve_classe_link;
DELETE FROM eleve_responsable_link;
DELETE FROM note;
DELETE FROM matiere;
DELETE FROM enseignant;
DELETE FROM classe;
DELETE FROM niveau;
DELETE FROM eleve;
DELETE FROM etablissement;
DELETE FROM medecin;
DELETE FROM responsable;
DELETE FROM ville;
DELETE FROM pays;

-- reset les sequences avec postgres
ALTER SEQUENCE pays_pays_id_seq RESTART WITH 1;
ALTER SEQUENCE ville_ville_id_seq RESTART WITH 1;
ALTER SEQUENCE etablissement_etablissement_id_seq RESTART WITH 1;
ALTER SEQUENCE medecin_medecin_id_seq RESTART WITH 1;
ALTER SEQUENCE eleve_eleve_id_seq RESTART WITH 1;
ALTER SEQUENCE responsable_responsable_id_seq RESTART WITH 1;
ALTER SEQUENCE eleve_responsable_link_eleve_responsable_id_seq RESTART WITH 1;
ALTER SEQUENCE matiere_matiere_id_seq RESTART WITH 1;
ALTER SEQUENCE niveau_niveau_id_seq RESTART WITH 1;
ALTER SEQUENCE classe_classe_id_seq RESTART WITH 1;
ALTER SEQUENCE eleve_classe_link_eleve_classe_link_id_seq RESTART WITH 1;
ALTER SEQUENCE enseignant_enseignant_id_seq RESTART WITH 1;
ALTER SEQUENCE classe_matiere_link_classe_matiere_link_id_seq RESTART WITH 1;
ALTER SEQUENCE note_note_id_seq RESTART WITH 1;


-- insertion des nouvelles valeurs
INSERT INTO pays(code_pays, pays) VALUES('FR','France');
INSERT INTO pays(code_pays, pays) VALUES('GB','Royaume-Uni');
INSERT INTO pays(code_pays, pays) VALUES('DE','Allemagne');
INSERT INTO pays(code_pays, pays) VALUES('ES','Espagne');
INSERT INTO pays(code_pays, pays) VALUES('US','Etats-Unis');
INSERT INTO pays(code_pays, pays) VALUES('JP','Japon');
INSERT INTO pays(code_pays, pays) VALUES('KR','Corée, Republique de');
INSERT INTO pays(code_pays, pays) VALUES('IT','Italie');
INSERT INTO pays(code_pays, pays) VALUES('CH','Suisse');
INSERT INTO pays(code_pays, pays) VALUES('BE','Belgique');
INSERT INTO pays(code_pays, pays) VALUES('PL','Pologne');
INSERT INTO pays(code_pays, pays) VALUES('PT','Portugal');
INSERT INTO pays(code_pays, pays) VALUES('GR','Grece');
INSERT INTO pays(code_pays, pays) VALUES('HU','Hongrie');
INSERT INTO pays(code_pays, pays) VALUES('CN','Chine');
INSERT INTO pays(code_pays, pays) VALUES('AU','Australie');

INSERT INTO niveau(nom) VALUES('seconde');
INSERT INTO niveau(nom) VALUES('première');
INSERT INTO niveau(nom) VALUES('terminale');

INSERT INTO matiere(nom) VALUES('anglais');
INSERT INTO matiere(nom) VALUES('français');
INSERT INTO matiere(nom) VALUES('espagnol');
INSERT INTO matiere(nom) VALUES('allemand');
INSERT INTO matiere(nom) VALUES('japonais');
INSERT INTO matiere(nom) VALUES('mathématiques');
INSERT INTO matiere(nom) VALUES('philosophie');
INSERT INTO matiere(nom) VALUES('SES');
INSERT INTO matiere(nom) VALUES('histoire - géographie');
INSERT INTO matiere(nom) VALUES('physique');
INSERT INTO matiere(nom) VALUES('chimie');
INSERT INTO matiere(nom) VALUES('SVT');
INSERT INTO matiere(nom) VALUES('EPS');
INSERT INTO matiere(nom) VALUES('SI');

-- dependances niveau 1

INSERT INTO ville(code_postal, ville, pays_fk) SELECT '75001','Paris 1e', pays_id FROM pays WHERE code_pays = 'FR';
INSERT INTO ville(code_postal, ville, pays_fk) SELECT '94807','Villejuif', pays_id FROM pays WHERE code_pays = 'FR';
INSERT INTO ville(code_postal, ville, pays_fk) SELECT '91420','Morangis', pays_id FROM pays WHERE code_pays = 'FR';
INSERT INTO ville(code_postal, ville, pays_fk) SELECT '94321','Thiais', pays_id FROM pays WHERE code_pays = 'FR';
INSERT INTO ville(code_postal, ville, pays_fk) SELECT '91380','Chilly-mazarin', pays_id FROM pays WHERE code_pays = 'FR';
INSERT INTO ville(code_postal, ville, pays_fk) SELECT '10871','Berlin', pays_id FROM pays WHERE code_pays = 'DE';
INSERT INTO ville(code_postal, ville, pays_fk) SELECT '75004','Paris 4e', pays_id FROM pays WHERE code_pays = 'FR';
INSERT INTO ville(code_postal, ville, pays_fk) SELECT '75015','Paris 15e', pays_id FROM pays WHERE code_pays = 'FR';
INSERT INTO ville(code_postal, ville, pays_fk) SELECT '75014','Paris 14e', pays_id FROM pays WHERE code_pays = 'FR';

INSERT INTO classe(nom, annee, niveau_fk) SELECT '2-1', 2011, niveau_id FROM niveau WHERE nom = 'seconde';
INSERT INTO classe(nom, annee, niveau_fk) SELECT '2-2', 2011, niveau_id FROM niveau WHERE nom = 'seconde';
INSERT INTO classe(nom, annee, niveau_fk) SELECT '2-3', 2011, niveau_id FROM niveau WHERE nom = 'seconde';
INSERT INTO classe(nom, annee, niveau_fk) SELECT '1-1', 2011, niveau_id FROM niveau WHERE nom = 'première';
INSERT INTO classe(nom, annee, niveau_fk) SELECT '1-2', 2011, niveau_id FROM niveau WHERE nom = 'première';
INSERT INTO classe(nom, annee, niveau_fk) SELECT '1-3', 2011, niveau_id FROM niveau WHERE nom = 'première';
INSERT INTO classe(nom, annee, niveau_fk) SELECT 'T-1', 2011, niveau_id FROM niveau WHERE nom = 'terminale';
INSERT INTO classe(nom, annee, niveau_fk) SELECT 'T-2', 2011, niveau_id FROM niveau WHERE nom = 'terminale';
INSERT INTO classe(nom, annee, niveau_fk) SELECT 'T-3', 2011, niveau_id FROM niveau WHERE nom = 'terminale';

-- dependances niveau 2

INSERT INTO medecin(nom, prenom, telephone, rue, email, ville_fk) SELECT 'Curie','Marie','0165748503','36 quai de Béthune','',ville_id FROM ville WHERE code_postal = '75004';
INSERT INTO medecin(nom, prenom, telephone, rue, email, ville_fk) SELECT 'Pasteur','Louis','0145679456','25, rue du Docteur-Roux','',ville_id FROM ville WHERE code_postal = '75015';

INSERT INTO enseignant(nom, prenom, rue, ville_fk) SELECT 'Dutronc','Marc','6 avenue des champs fou', ville_id FROM ville WHERE code_postal = '94807';
INSERT INTO enseignant(nom, prenom, rue, ville_fk) SELECT 'Kent','Clarc','15 rue du GDG', ville_id FROM ville WHERE code_postal = '94321';
INSERT INTO enseignant(nom, prenom, rue, ville_fk) SELECT 'Parker','Peter','1 near DTC', ville_id FROM ville WHERE code_postal = '75001';

INSERT INTO etablissement(nom, rue, telephone, ville_fk) SELECT 'college lambda','1 rue du lambda','0164758964', ville_id FROM ville WHERE code_postal = '75001';
INSERT INTO etablissement(nom, rue, telephone, ville_fk) SELECT 'Sainte Anne','1 rue Cabanis','0145658000', ville_id FROM ville WHERE code_postal = '75014';

INSERT INTO classe_matiere_link(classe_fk, matiere_fk, enseignant_fk) VALUES (1,7,2);
INSERT INTO classe_matiere_link(classe_fk, matiere_fk, enseignant_fk) VALUES (1,2,1);
INSERT INTO classe_matiere_link(classe_fk, matiere_fk, enseignant_fk) VALUES (1,1,3);
INSERT INTO classe_matiere_link(classe_fk, matiere_fk, enseignant_fk) VALUES (2,7,2);
INSERT INTO classe_matiere_link(classe_fk, matiere_fk, enseignant_fk) VALUES (2,2,1);
INSERT INTO classe_matiere_link(classe_fk, matiere_fk, enseignant_fk) VALUES (2,1,3);
INSERT INTO classe_matiere_link(classe_fk, matiere_fk, enseignant_fk) VALUES (3,7,2);
INSERT INTO classe_matiere_link(classe_fk, matiere_fk, enseignant_fk) VALUES (3,2,1);
INSERT INTO classe_matiere_link(classe_fk, matiere_fk, enseignant_fk) VALUES (3,1,3);

INSERT INTO eleve(matricule, nom, prenom, date_naissance, ville_naissance_fk, nationalite_fk, sexe, rue, ville_fk, email, date_inscription, photo, etablissement_precedent_fk, tel_domicile, tel_mobile, tel_mobile_2, medecin_fk, remarques_medicales, vaccinations, allergies)
VALUES ('F557533','greze','pierrick', date '1991-09-27', 1, 1, 'h', '5 avenue du petit moulin', 2, 'lolilol@gmail.com', date '2011-09-01', NULL, 1, '','0665748576','', 1, 'Existence vitale en cours de confirmation par le CHU de petit Moulin', 'ras', 'ras');
INSERT INTO eleve(matricule, nom, prenom, date_naissance, ville_naissance_fk, nationalite_fk, sexe, rue, ville_fk, email, date_inscription, photo, etablissement_precedent_fk, tel_domicile, tel_mobile, tel_mobile_2, medecin_fk, remarques_medicales, vaccinations, allergies)
VALUES ('F557534','bily','clément', date '1991-04-23', 1, 1, 'h', '13 avenue des champs fous', 2, 'clem@gmail.com', date '2011-09-01', NULL, 1, '','0665748577','', 2, 'ras', 'ras', 'ras');
INSERT INTO eleve(matricule, nom, prenom, date_naissance, ville_naissance_fk, nationalite_fk, sexe, rue, ville_fk, email, date_inscription, photo, etablissement_precedent_fk, tel_domicile, tel_mobile, tel_mobile_2, medecin_fk, remarques_medicales, vaccinations, allergies)
VALUES ('F557535','croizier','romain', date '1991-01-01', 1, 1, 'h', 'chez ses parents', 2, 'romain@gmail.com', date '2011-09-01', NULL, 1, '','0665748578','', 2, 'ras', 'ras', 'ras');
INSERT INTO eleve(matricule, nom, prenom, date_naissance, ville_naissance_fk, nationalite_fk, sexe, rue, ville_fk, email, date_inscription, photo, etablissement_precedent_fk, tel_domicile, tel_mobile, tel_mobile_2, medecin_fk, remarques_medicales, vaccinations, allergies)
VALUES ('F557536','hammer','julien', date '1991-01-02', 1, 1, 'h', 'a fugué ...', 2, 'julien@gmail.com', date '2011-09-01', NULL, 1, '','0665748579','', 2, 'ras', 'ras', 'ras');

INSERT INTO eleve_classe_link(classe_fk, eleve_fk) SELECT c.classe_id, e.eleve_id FROM eleve e, classe c 
	WHERE e.matricule = 'F557533' AND c.annee = 2011 AND c.nom = '2-1';
INSERT INTO eleve_classe_link(classe_fk, eleve_fk) SELECT c.classe_id, e.eleve_id FROM eleve e, classe c 
	WHERE e.matricule = 'F557534' AND c.annee = 2011 AND c.nom = '2-1';
INSERT INTO eleve_classe_link(classe_fk, eleve_fk) SELECT c.classe_id, e.eleve_id FROM eleve e, classe c 
	WHERE e.matricule = 'F557535' AND c.annee = 2011 AND c.nom = '2-2';

COMMIT;

